IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.f_GetIdxCompressionInfo'))
DROP FUNCTION MD3.f_GetIdxCompressionInfo
GO

--returns filegroup and compression settings for the given index.  

CREATE FUNCTION MD3.f_GetIdxCompressionInfo
( 
	@SchemaName SYSNAME
   ,@TableName SYSNAME
   ,@IndexName  SYSNAME  
)
RETURNS VARCHAR(255)
AS 
	
BEGIN
	DECLARE @Compression varchar(255);

	SELECT TOP 1 @Compression = 
		p.data_compression_desc
	FROM sys.objects o 
	INNER JOIN  sys.indexes i
		ON i.object_id = o.object_id
	INNER JOIN  sys.partitions p 
		ON p.object_id = o.object_id  
		 AND i.index_id = p.index_id
	INNER JOIN  sys.allocation_units au
		ON au.container_id = p.hobt_id
	INNER JOIN  sys.filegroups fg
		ON fg.data_space_id = au.data_space_id
	WHERE o.type = 'U' AND i.type > 0
		AND  o.name = @TableName
		AND o.schema_id = schema_id(@SchemaName)
		AND i.name = @IndexName
	ORDER BY p.data_compression_desc, p.partition_number
	
	RETURN @Compression
END
GO
